import mysql.connector
from datetime import datetime
import os
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()


class DatabaseConnection:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', ''),
            database=os.getenv('DB_NAME', 'student_management')
        )
        self.cursor = self.connection.cursor()

    def __enter__(self):
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.connection.commit()
        self.cursor.close()
        self.connection.close()


class StudentManager:
    @staticmethod
    def add_student(student_id, name, gender, birth_date, class_name):
        with DatabaseConnection() as cursor:
            sql = """INSERT INTO students (student_id, name, gender, birth_date, class_name, admission_date)
                    VALUES (%s, %s, %s, %s, %s, %s)"""
            cursor.execute(sql, (student_id, name, gender,
                           birth_date, class_name, datetime.now().date()))

    @staticmethod
    def get_student(student_id):
        with DatabaseConnection() as cursor:
            cursor.execute(
                "SELECT * FROM students WHERE student_id = %s", (student_id,))
            return cursor.fetchone()

    @staticmethod
    def update_student(student_id, name=None, gender=None, birth_date=None, class_name=None):
        with DatabaseConnection() as cursor:
            updates = []
            values = []
            if name:
                updates.append("name = %s")
                values.append(name)
            if gender:
                updates.append("gender = %s")
                values.append(gender)
            if birth_date:
                updates.append("birth_date = %s")
                values.append(birth_date)
            if class_name:
                updates.append("class_name = %s")
                values.append(class_name)

            if updates:
                sql = f"UPDATE students SET {', '.join(updates)} WHERE student_id = %s"
                values.append(student_id)
                cursor.execute(sql, tuple(values))


class CourseManager:
    @staticmethod
    def add_course(course_id, course_name, credit, teacher_name):
        with DatabaseConnection() as cursor:
            sql = """INSERT INTO courses (course_id, course_name, credit, teacher_name)
                    VALUES (%s, %s, %s, %s)"""
            cursor.execute(sql, (course_id, course_name, credit, teacher_name))

    @staticmethod
    def get_course(course_id):
        with DatabaseConnection() as cursor:
            cursor.execute(
                "SELECT * FROM courses WHERE course_id = %s", (course_id,))
            return cursor.fetchone()


class ScoreManager:
    @staticmethod
    def add_score(student_id, course_id, score, semester):
        with DatabaseConnection() as cursor:
            sql = """INSERT INTO scores (student_id, course_id, score, semester)
                    VALUES (%s, %s, %s, %s)"""
            cursor.execute(sql, (student_id, course_id, score, semester))

    @staticmethod
    def get_student_scores(student_id):
        with DatabaseConnection() as cursor:
            sql = """
                SELECT s.semester, c.course_name, s.score
                FROM scores s
                JOIN courses c ON s.course_id = c.course_id
                WHERE s.student_id = %s
                ORDER BY s.semester, c.course_name
            """
            cursor.execute(sql, (student_id,))
            return cursor.fetchall()

    @staticmethod
    def calculate_average(student_id, semester=None):
        with DatabaseConnection() as cursor:
            sql = """
                SELECT AVG(score) as average_score
                FROM scores
                WHERE student_id = %s
            """
            params = [student_id]
            if semester:
                sql += " AND semester = %s"
                params.append(semester)

            cursor.execute(sql, tuple(params))
            return cursor.fetchone()[0]


def main():
    # 示例使用
    try:
        # 添加学生
        student_mgr = StudentManager()
        student_mgr.add_student("2024001", "张三", "M", "2000-01-01", "计算机科学1班")

        # 添加课程
        course_mgr = CourseManager()
        course_mgr.add_course("CS001", "Python程序设计", 3.0, "李老师")

        # 录入成绩
        score_mgr = ScoreManager()
        score_mgr.add_score("2024001", "CS001", 85.5, "2024春季")

        # 查询学生成绩
        scores = score_mgr.get_student_scores("2024001")
        print("学生成绩:", scores)

        # 计算平均分
        avg_score = score_mgr.calculate_average("2024001")
        print(f"平均分: {avg_score:.2f}")

    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
    except Exception as e:
        print(f"系统错误: {e}")


if __name__ == "__main__":
    main()
